IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SaveCOIReviewNote]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[SaveCOIReviewNote]
GO

SET QUOTED_IDENTIFIER OFF
GO
/******************************************************************                
* Name:   [SaveCOIReviewNote]                
* Purpose:  Saves the review Note for the COI, adding a map for the same in COI_ReviewNote_map  
* PARAMETERS(IN)                
* Name          Description                     
* -------------      -------------------------------------------                
* @noteId       Note Id(Primary key)  
* @cOIId int,   COIId, to which the notes have to be added        
* @reviewNote Content to be added                   
*********************************************************************/   
  
CREATE PROCEDURE dbo.[SaveCOIReviewNote] --1, 2, '\n 10/21/2008 Approved'      
(      
 @cOIId int,       
 @reviewNote varchar(max)      
)      
AS BEGIN      
Declare @currentDate DateTime      
Declare @noteId int    
    
      
Set @currentDate  = GetDate();      
 If not exists (Select 1 from COI_ReviewNote_Map where COiId = @cOIId)    
  BEGIN       
  --Select  @reviewNote      
   --Add New Note for the COI      
   Insert Into dbo.[ReviewNote]( ReviewNote, DateCreated)       
   values (@reviewNote,@currentDate)      
       
   SELECT @noteId = IDENT_CURRENT('ReviewNote')       
   if @noteId <> 0       
    Insert Into dbo.[COI_ReviewNote_Map](COIId,NoteId) Values (@coiId,@noteId)      
  END       
 else      
  BEGIN      
 SELECT @noteId = NoteId from COI_ReviewNote_Map where COiId = @cOIId    
  --Update(Add new note to existing note)      
  Declare @existingReviewNote varchar(Max)      
  Set @existingReviewNote = (Select ReviewNote from ReviewNote where Id = @noteId)      
       
  Update [ReviewNote] set       
    ReviewNote = @existingReviewNote + @reviewNote,      
    DateModified = @currentDate       
  Where Id = @noteId       
  END      
END 




